-- @owner: wang_zhengyuan
-- @date: 2022/02/11
-- @testpoint: 前置条件-建表1

--step1:建表;expect:建表成功
drop table if exists table_idx_in_001;

create table table_idx_in_001(
staff_id int not null,
course_id bigint ,
staff_number number(38,12),
course_name varchar(500) ,
higest_degree varchar2(500) not null,
course_start_date date,
course_end_date date,
exam_date date,
score int)
 with (storage_type=ustore);
 
--step2:创建索引;expect:创建索引成功
drop index if exists idx_in_001_001;
 
create index idx_in_001_001 on table_idx_in_001(staff_id,course_name desc);
 
--step3:创建索引;expect:创建索引成功
drop index if exists idx_in_001_002;
 
create index idx_in_001_002 on table_idx_in_001(course_id,staff_number asc,course_start_date);
 
--step4:创建sequence;expect:创建sequence成功
drop sequence if exists v_seq;
 
create sequence v_seq increment by 1 start with 1;
 
--step5:插入数据;expect:插入数据成功
insert into table_idx_in_001 values(1 ,v_seq.nextval,4041.04329387, 'english','doctor','2017-06-15 12:00:00','2017-06-20 12:00:00','2017-06-25 12:00:00',90);
 
insert into table_idx_in_001 values(2 ,v_seq.nextval,4005.70237548, 'painting','scholar','2018-06-15 12:00:00','2018-06-20 12:00:00','2018-06-25 12:00:00',50);
 
insert into table_idx_in_001 values(3 ,v_seq.nextval,4030.1242131 , 'drawing','doctor',null,'2018-06-27 12:00:00','2018-06-29 12:00:00',95);
 
insert into table_idx_in_001 values(4 ,v_seq.nextval,4001.87754238 , null,'master','2017-06-15 12:00:00','2017-06-20 12:00:00','2017-06-25 12:00:00',90);
 
insert into table_idx_in_001 values(5 ,v_seq.nextval,4046.25584633 , 'system','master','2018-06-15 12:00:00','2018-06-20 12:00:00','2018-06-25 12:00:00',89);
 
insert into table_idx_in_001 values(6 ,v_seq.nextval,4003.68558645 , 'language','scholar','2018-06-25 12:00:00','2018-06-27 12:00:00','2018-06-29 12:00:00',95);
 
insert into table_idx_in_001 values(7 ,v_seq.nextval,4044.9145853 , 'grammer','doctor','2017-06-15 12:00:00',null,'2017-06-25 12:00:00',90);
 
insert into table_idx_in_001 values(8 ,v_seq.nextval,4035.26588922 , 'chinese','scholar','2018-06-15 12:00:00','2018-06-20 12:00:00','2018-06-25 12:00:00',88);
 
insert into table_idx_in_001 values(9 ,v_seq.nextval,4011.51183805 , 'math','doctor','2018-06-25 12:00:00','2018-06-27 12:00:00','2018-06-29 12:00:00',95);
 
insert into table_idx_in_001 values(10,v_seq.nextval,4018.35133361 , 'sql majorization','doctor','2017-06-15 12:00:00','2017-06-20 12:00:00','2017-06-25 12:00:00',90);
 
insert into table_idx_in_001 values(11,v_seq.nextval,4041.62109974 , 'big data','master','2018-06-15 12:00:00','2018-06-20 12:00:00','2018-06-25 12:00:00',75);
 
insert into table_idx_in_001 values(12,v_seq.nextval,4018.19233257 , 'performance turning','scholar','2018-06-25 12:00:00','2018-06-27 12:00:00',null,95);
 
insert into table_idx_in_001 values(1 ,v_seq.nextval,4041.04329387, 'english','doctor','2017-06-15 12:00:00','2013-06-10 12:00:00','2017-06-25 12:00:00',90);
 
insert into table_idx_in_001 values(2 ,v_seq.nextval,4041.04329387, 'painting','scholar','2018-06-15 12:00:00','2018-06-20 12:00:00','2018-06-25 12:00:00',50);
 
insert into table_idx_in_001 values(3 ,v_seq.nextval,4042.04329387, 'drawing','doctor',null,'2018-06-27 12:00:00','2018-06-29 12:00:00',null);
 
insert into table_idx_in_001 values(4 ,v_seq.nextval,4041.04322387, null,'master','2017-06-15 12:00:00',null,'2017-06-25 12:00:00',90);
 
insert into table_idx_in_001 values(5 ,v_seq.nextval,4041.04329387, 'system','master','2018-06-15 12:00:00','2018-06-20 12:00:00','2018-06-25 12:00:00',89);
 
insert into table_idx_in_001 values(6 ,v_seq.nextval,4041.04322387, 'language','scholar','2018-06-25 12:00:00','2018-06-27 12:23:00','2018-06-29 12:00:00',95);
 
insert into table_idx_in_001 values(7 ,v_seq.nextval,4041.04339387, 'grammer','doctor','2017-06-15 12:00:00',null,'2017-06-25 12:00:00',90);
 
insert into table_idx_in_001 values(8 ,v_seq.nextval,4041.04329387, 'chinese','scholar','2018-06-15 12:00:00','2018-06-20 12:00:00','2018-06-25 12:00:00',null);
 
insert into table_idx_in_001 values(9 ,v_seq.nextval,4041.14329387, 'math',' ','2018-06-25 12:00:00','2018-06-27 12:00:00','2018-06-29 12:00:00',95);
 
insert into table_idx_in_001 values(10,v_seq.nextval,4045.04329387, null,'doctor','2017-06-15 12:00:00','2017-06-20 12:00:00','2017-06-25 12:00:00',90);
 
insert into table_idx_in_001 values(11,v_seq.nextval,4041.04329489387, 'big data','master','2018-06-15 12:00:00',null,'2018-06-25 12:00:00',75);
 
insert into table_idx_in_001 values(12,v_seq.nextval,4041.0432459387, 'performance turning','scholar','2018-06-25 12:00:00','2018-06-27 12:00:00',null,95);
 
begin
    for i in 1..300 loop
    insert into table_idx_in_001 values(mod(i,13),v_seq.nextval,v_seq.nextval/i,'big data','doctor','2018-06-15 12:00:00',add_months('2018-06-29 12:00:00',mod(i,6)),'2017-06-25 12:00:00',80+mod(i,15));
    end loop;
end;
/
 
begin
    for i in 1..300 loop
    insert into table_idx_in_001 values(mod(i,11),v_seq.nextval,v_seq.nextval/i,'performance turning','scholar','2018-06-15 12:00:00','2017-06-25 12:00:00',add_months('2018-06-29 12:00:00',trunc(i/6)),50+i);
    end loop;
end;
/
 
begin
    for i in 1..300 loop
    insert into table_idx_in_001 values(mod(i,8),v_seq.nextval,v_seq.nextval/i-5,'sql majorization','master',add_months('2018-06-29 12:00:00',mod(i,6)),'2018-06-15 12:00:00',add_months('2017-06-25 12:00:00',mod(i,3)),90-mod(i,15));
    end loop;
end;
/
 
--step6:建表;expect:建表成功
drop table if exists table_idx_in_school_002;
 
create table table_idx_in_school_002(
staff_id int not null,
higest_degree varchar(8),
graduate_school varchar(64),
graduate_date date,
t_sql_note VARCHAR(70)) with (storage_type=ustore);
 
--step7:创建索引;expect:创建索引成功
drop index if exists idx_in_002_001;
 
create index idx_in_002_001 on table_idx_in_school_002(staff_id asc,graduate_school) ;
 
--step8:创建索引;expect:创建索引成功
drop index if exists idx_in_002_002;
 
create index idx_in_002_002 on table_idx_in_school_002(higest_degree,graduate_date,t_sql_note) initrans 50 ;
 
--step9:创建sequence;expect:创建sequence成功
drop sequence if exists v_seq;
 
create sequence v_seq increment by 2 start with 1;
 
--step10:插入数据;expect:插入数据成功
insert into table_idx_in_school_002 values(v_seq.nextval,'doctor','xidian university','2017-07-06 12:00:00','211');
 
insert into table_idx_in_school_002 values(v_seq.nextval-1,'doctor','xidian university','2017-07-06 12:00:00','211');
 
insert into table_idx_in_school_002 values(v_seq.nextval,'master','northwestern polytechnical university','2017-07-06 12:00:00','211&985');
 
insert into table_idx_in_school_002 values(v_seq.nextval-1,'scholar','peking university','2017-07-06 12:00:00','211&985');
 
insert into table_idx_in_school_002 values(v_seq.nextval,'scholar','peking university','2017-07-06 12:00:00','211&985');
 
insert into table_idx_in_school_002 values(v_seq.nextval-1,'doctor','xidian university','2017-07-06 12:00:00','211');
 
insert into table_idx_in_school_002 values(v_seq.nextval,'doctor','xidian university','2017-07-06 12:00:00','211');
 
insert into table_idx_in_school_002 values(v_seq.nextval,'master','northwestern polytechnical university','2017-07-06 12:00:00','211&985');
 
insert into table_idx_in_school_002 values(v_seq.nextval,'scholar','peking university','2017-07-06 12:00:00','211&985');
 
begin
    for i in 1..300 loop
    insert into table_idx_in_school_002 values(v_seq.nextval,'scholar','xidian university',add_months('2017-06-15 12:00:00',mod(i,5)),'211');
    end loop;
end;
/
 
begin
    for i in 1..300 loop
    insert into table_idx_in_school_002 values(v_seq.nextval/2,'master','peking university',add_months('2017-02-15 12:00:00',mod(i,12)),'211&985');
    end loop;
end;
/
 
begin
    for i in 1..300 loop
    insert into table_idx_in_school_002 values(i,'doctor','northwestern polytechnical university',add_months('2016-02-15 12:00:00',mod(i,8)),'211&985');
    end loop;
end;
/
 
